package org.morningangel.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.naming.NamingException;

import org.morningangel.bean.Activity;
import org.morningangel.dao.ActivityDao;
import org.morningangel.util.DBHelperUtil;

public class ActivityDaoImpl implements ActivityDao {

	@Override
	public Integer save(Activity activity) {
		Connection conn=null;
		PreparedStatement ps=null;
		Integer suNum = 0;
		String sql="insert into tb_activity (title,mainImage,image,startTime,endTime,type,address,sponsor,description,url,status,publishTime) values(?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			conn=DBHelperUtil.getConnection();
			//打开事务
			conn.setAutoCommit(false);
			ps=conn.prepareStatement(sql);
			ps.setString(1, activity.getTitle());
			ps.setString(2, activity.getMainImage());
			ps.setString(3, activity.getImage());
			ps.setTimestamp(4, new Timestamp(activity.getStartTime().getTime()));
			ps.setTimestamp(5, new Timestamp(activity.getEndTime().getTime()));
			ps.setString(6, activity.getType());
			ps.setString(7, activity.getAddress());
			ps.setString(8, activity.getSponsor());
			ps.setString(9, activity.getDescription());
			ps.setString(10, activity.getUrl());
			ps.setInt(11, activity.getStatus());
			ps.setTimestamp(12, new Timestamp(activity.getPublishTime().getTime()));
			suNum=ps.executeUpdate();
			//提交事务
			conn.commit();
			//关闭事务
			conn.setAutoCommit(true);
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBHelperUtil.close(ps, conn);
		return suNum;
	}

	@Override
	public Activity getById(Integer id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Activity activity=null;
		String sql="select id, title, mainImage, image, startTime, endTime, type, address, sponsor, description, url, status, publishTime, partipateNum, interestNum from tb_activity where id=?";
		try {
			conn=DBHelperUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next())
			{
				activity=new Activity();
				setActivity(rs, activity);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBHelperUtil.close(rs, ps, conn);
		return activity;
	}

	@Override
	public Integer update(Activity activity) {
		Connection conn=null;
		PreparedStatement ps=null;
		Integer suNum=0;
		String sql="update tb_activity title = ?, mainImage=?,image=?,startTime=?,endTime=?,type=?,adrress=?,sponsor=?,description=?,url=? where id=?";
		try {
			conn=DBHelperUtil.getConnection();
			conn.setAutoCommit(false);
			ps=conn.prepareStatement(sql);
			ps.setString(1, activity.getTitle());
			ps.setString(2, activity.getMainImage());
			ps.setString(3, activity.getImage());
			ps.setTimestamp(4, new Timestamp(activity.getStartTime().getTime()));
			ps.setTimestamp(5, new Timestamp(activity.getEndTime().getTime()));
			ps.setString(6, activity.getType());
			ps.setString(7, activity.getAddress());
			ps.setString(8, activity.getSponsor());
			ps.setString(9, activity.getDescription());
			ps.setString(10, activity.getUrl());
			ps.setInt(11, activity.getId());
			suNum=ps.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBHelperUtil.close(ps, conn);
		return suNum;
	}

	@Override
	public Integer update(Integer id, Integer status) {
		Connection conn=null;
		PreparedStatement ps=null;
		Integer suNum=0;
		String sql="update tb_activity set status=? where id = ?";
		try {
			conn=DBHelperUtil.getConnection();
			//打开事务
			conn.setAutoCommit(false);
			ps=conn.prepareStatement(sql);
			ps.setInt(1, status);
			ps.setInt(2, id);
			suNum=ps.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBHelperUtil.close(ps, conn);
		
		return suNum;
	}

	@Override
	public Integer count(Integer status) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select count(*) from tb_activity where status=?";
		Integer count=0;
		try {
			conn=DBHelperUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, status);
			rs=ps.executeQuery();
			if(rs.next())
			{
				count=rs.getInt(1);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBHelperUtil.close(rs, ps, conn);
	    return count;
	}

	@Override
	public List<Activity> listPage(Integer pageNum, Integer pageSize,
			Integer status) {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		Activity activity=null;
		List<Activity> list=new ArrayList<Activity>();
		try {
			conn=DBHelperUtil.getConnection();
			String sql="select id, title, mainImage, image, startTime, endTime, type, address, sponsor, description, url, status, publishTime, partipateNum, interestNum from tb_activity where status = ? order by startTime desc limit ?,?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, status);
			ps.setInt(2, (pageNum-1) * pageSize);
			ps.setInt(3, pageSize);
			rs=ps.executeQuery();
			while(rs.next())
			{
				activity=new Activity();
				setActivity(rs, activity);
				list.add(activity);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBHelperUtil.close(rs, ps, conn);
		return list;
	}

	@Override
	public List<Activity> search(String key) {
		Connection conn = null;
		PreparedStatement ps = null;
		List<Activity> list = new ArrayList<Activity>();
		ResultSet rs = null;
		try {
			conn = DBHelperUtil.getConnection();
			String sql = "select id, title, mainImage, image, startTime, endTime, type, address, sponsor, description, url, status, publishTime, partipateNum, interestNum from tb_activity where (title like ? or type like ? or address like ? or sponsor like ? or description like ?) and status = ?";
			ps = conn.prepareStatement(sql);
			key = "%" + key + "%";
			ps.setString(1, key);
			ps.setString(2, key);
			ps.setString(3, key);
			ps.setString(4, key);
			ps.setString(5, key);
			ps.setInt(6, 1);
			rs = ps.executeQuery();
			while (rs.next()) {
				Activity activity = new Activity();
				setActivity(rs, activity);
				list.add(activity);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBHelperUtil.close(rs, ps, conn);
		
		return list;
	}

	@Override
	public Integer getCount() {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select count(*) from tb_activity";
		Integer count=0;
		try {
			conn=DBHelperUtil.getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next())
			{
				count=rs.getInt(1);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBHelperUtil.close(rs, ps, conn);
	    return count;

	}

	@Override
	public Integer updateMainImage(String mainImage, Timestamp ts) {
		Connection conn=null;
		PreparedStatement ps=null;
		Integer suNum=0;
		String sql="update tb_activity set mainImage=? where publishTime=?";
		try {
			conn=DBHelperUtil.getConnection();
			conn.setAutoCommit(false);
			ps=conn.prepareStatement(sql);
			ps.setString(1, mainImage);
			ps.setTimestamp(2, ts);
			suNum=ps.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBHelperUtil.close(ps, conn);
		return suNum;
	}
	
	private void setActivity(ResultSet rs, Activity activity) throws SQLException {
		activity.setId(rs.getInt(1));
		activity.setTitle(rs.getString(2));
		activity.setMainImage(rs.getString(3));
		activity.setImage(rs.getString(4));
		activity.setStartTime(new Date(rs.getTimestamp(5).getTime()));
		activity.setEndTime(new Date(rs.getTimestamp(6).getTime()));
		activity.setType(rs.getString(7));
		activity.setAddress(rs.getString(8));
		activity.setSponsor(rs.getString(9));
		activity.setDescription(rs.getString(10));
		activity.setUrl(rs.getString(11));
		activity.setStatus(rs.getInt(12));
		activity.setPublishTime(new Date(rs.getTimestamp(13).getTime()));
		activity.setPartipateNum(rs.getInt(14));
		activity.setInterestNum(rs.getInt(15));
	}

	@Override
	public List<Activity> listSearchPage(Integer maxId, Integer pageSize, String key) {
		Connection conn = null;
		PreparedStatement ps = null;
		List<Activity> list = new ArrayList<Activity>();
		ResultSet rs = null;
		try {
			conn = DBHelperUtil.getConnection();
			String sql = "select id, title, mainImage, image, startTime, endTime, type, address, sponsor, description, url, status, publishTime, partipateNum, interestNum from tb_activity where (title like ? or type like ? or address like ? or sponsor like ? or description like ?) and status = ? and id<? order by id desc limit ?";
			ps = conn.prepareStatement(sql);
			key = "%" + key + "%";
			ps.setString(1, key);
			ps.setString(2, key);
			ps.setString(3, key);
			ps.setString(4, key);
			ps.setString(5, key);
			ps.setInt(6, 1);
			ps.setInt(7, maxId);
			ps.setInt(8, pageSize);
			rs = ps.executeQuery();
			while (rs.next()) {
				Activity activity = new Activity();
				setActivity(rs, activity);
				list.add(activity);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBHelperUtil.close(rs, ps, conn);
		
		return list;
	}

	@Override
	public List<Activity> listNewPage(Integer pageSize, String key) {
		Connection conn = null;
		PreparedStatement ps = null;
		List<Activity> list = new ArrayList<Activity>();
		ResultSet rs = null;
		try {
			conn = DBHelperUtil.getConnection();
			String sql = "select id, title, mainImage, image, startTime, endTime, type, address, sponsor, description, url, status, publishTime, partipateNum, interestNum from tb_activity where (title like ? or type like ? or address like ? or sponsor like ? or description like ?) and status = ?  order by id desc limit ?";
			ps = conn.prepareStatement(sql);
			key = "%" + key + "%";
			ps.setString(1, key);
			ps.setString(2, key);
			ps.setString(3, key);
			ps.setString(4, key);
			ps.setString(5, key);
			ps.setInt(6, 1);
			ps.setInt(7, pageSize);
			rs = ps.executeQuery();
			while (rs.next()) {
				Activity activity = new Activity();
				setActivity(rs, activity);
				list.add(activity);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBHelperUtil.close(rs, ps, conn);
		
		return list;
	}

}
